--Create Database
go
use master
go 
if(DB_ID('InstituteOfFineArts')is not null)
drop database InstituteOfFineArts
go
create database InstituteOfFineArts
go
use InstituteOfFineArts
--Create tables
--Table Manager
go
create table Manager(
managerId int primary key identity(1,1),
userName varchar(100) unique,
pass varchar(100),
fullName varchar(100),
[address] varchar(100),
email varchar(100),
phoneNo varchar(15)
)
--Table Staff
go
create table Staff(
staffId int primary key identity(1,1),
userName varchar(100) unique,
pass varchar(100),
fullName varchar(100),
[address] varchar(100),
email varchar(100),
phoneNo varchar(15),
major varchar(15)
)
--Table Student
go
create table Student(
studentId int primary key identity(1,1),
userName varchar(100) unique,
pass varchar(100),
fullName varchar(100),
[address] varchar(100),
email varchar(100),
phoneNo varchar(15),
major varchar(15),
class varchar(15)
)
--Table Competition
go

create table Competition(
comId int primary key identity(1,1),
name varchar(100),
firstPrize varchar(100),
secondPrize varchar(100),
thirdPrize varchar(100),
postDate datetime,
startDate datetime,
endDate datetime,
[description] varchar(2000),
quotation varchar(500),
managerId int foreign key references Manager(managerId),
compImage varchar(50)
)
--Table Design
go
create table Design(
designId int primary key identity(1,1),
name varchar(100),
postDate datetime,
[description] varchar(2000),
quotation varchar(500),
designImage varchar(50),
studentId int foreign key references Student(studentId),
comId int foreign key references Competition(comId)
)
--Table Staff-Competition
go
create table StaffCompetition(
Id int primary key identity(1,1),
staffId int foreign key references Staff(staffId),
comId int foreign key references Competition(comId)
)
--Table Mark
go
create table Mark(
markId int primary key identity(1,1),
staffId int foreign key references Staff(staffId),
designId int foreign key references Design(designId),
mark float
)
go
alter table Competition add constraint checkDate check(datediff(day,startDate,endDate)>0)
go
create trigger insertComp
on Competition
after insert
as
	begin
	declare @comId int;
	declare @comImg varchar(50);
	declare @newComImg varchar(50);
	select @comId=comId from inserted;
	select @comImg=compImage from inserted;
	set @newComImg=CONVERT(varchar(10),@comId)+@comImg;
	update Competition set compImage=@newComImg where comId=@comId
	end
go
create trigger insertDes
on Design
after insert
as
	begin
	declare @designId int;
	declare @designImg varchar(50);
	declare @newDesignImg varchar(50);
	select @designId=designId from inserted;
	select @designImg=designImage from inserted;
	set @newDesignImg=CONVERT(varchar(10),@designId)+@designImg;
	update Design set designImage=@newDesignImg where designId=@designId
	end
go
create proc insertCompetition
	@name varchar(100),
	@postDate datetime,
	@startDate datetime,
	@endDate datetime,
	@firstPrize varchar(100),
	@secondPrize varchar(100),
	@thirdPrize varchar(100),
	@description varchar(2000),
	@quotation varchar(500),
	@managerId int,
	@compImage varchar(50)
  as
	  begin
		insert into Competition(name,postDate,startDate,endDate,firstPrize,secondPrize,thirdPrize,[description],quotation,managerId,compImage)values(@name,@postDate,@startDate,@endDate,@firstPrize,@secondPrize,@thirdPrize,@description,@quotation,@managerId,@compImage)
		select SCOPE_IDENTITY()as compImg
	  end
 go
 create proc insertDesign
	@name varchar(100),
	@postDate datetime,
	@description varchar(2000),
	@quotation varchar(500),
	@designImage varchar(50),
	@studentId int,
	@comId int
  as
	  begin
		insert into Design(name,postDate,[description],quotation,designImage,studentId,comId) values (@name,@postDate,@description,@quotation,@designImage,@studentId,@comId)
		select SCOPE_IDENTITY()as designImg
	  end
 go
 select top 1 comId,name from Competition where DATEDIFF(D,endDate,GETDATE())>0 order by DATEDIFF(D,startDate,GETDATE())
 select top 3 c.name,m.designId,d.name as designName,AVG(m.mark) as SumMark from Mark m inner join Design d on m.designId=d.designId inner join Competition c on d.comId=c.comId where d.comId=3 group by m.designId,c.name,d.name order by SumMark desc
 select top 1 c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage,COUNT(d.comId) as countDesign from Competition c inner join Design d on c.comId=d.comId where DATEDIFF(D,startDate,GETDATE())>0 and DATEDIFF(D,endDate,GETDATE())<0 group by c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage order by countDesign desc
insert into Staff(userName,pass,fullName,[address],email,phoneNo,major)values ('staff1','123456','staff1 name','staff1 address','staff1 email','staff1number','staff1 major')
insert into Staff(userName,pass,fullName,[address],email,phoneNo,major)values ('staff2','123456','staff2 name','staff2 address','staff2 email','staff2number','staff2 major')
insert into Staff(userName,pass,fullName,[address],email,phoneNo,major)values ('staff3','123456','staff3 name','staff3 address','staff3 email','staff3number','staff3 major')
insert into Manager(userName,pass,fullName,[address],email,phoneNo)values('manager1','123456','manager1 fullname','manager1 address','manager1 email','manager1number')
insert into Student(userName,pass,fullName,[address],email,phoneNo,major,class)values('student1','123456','st1 name','st1address','st1email','st1number','st1major','st1class')
insert into Student(userName,pass,fullName,[address],email,phoneNo,major,class)values('student2','123456','st1 name','st1address','st1email','st1number','st1major','st1class')
insert into Student(userName,pass,fullName,[address],email,phoneNo,major,class)values('student3','123456','st2 name','st1address','st1email','st1number','st1major','st1class')

exec insertCompetition 'com name 1','1/4/2014','1/7/2014','1/31/2014','1000 USD','100 USD','10 USD','description 1','quotation 1',1,'.jpg'
exec insertCompetition 'com name 2','1/4/2013','1/7/2013','1/31/2013','1000 USD','100 USD','10 USD','description 2','quotation 2',1,'.jpg'
exec insertCompetition 'com name 3','1/4/2013','1/7/2013','1/31/2013','1000 USD','100 USD','10 USD','description 3','quotation 3',1,'.jpg'
exec insertCompetition 'com name 4','1/4/2014','1/3/2014','1/31/2015','1000 USD','100 USD','10 USD','description 4','quotation 4',1,'.jpg'
exec insertCompetition 'com name 5','1/4/2014','1/1/2014','1/31/2016','1000 USD','100 USD','10 USD','description 5','quotation 5',1,'.jpg'
exec insertCompetition 'com name 6','1/4/2014','1/2/2014','1/31/2014','1000 USD','100 USD','10 USD','description 6','quotation 6',1,'.jpg'
exec insertCompetition 'com name 7','1/4/2013','1/7/2014','1/31/2014','1000 USD','100 USD','10 USD','description 6','quotation 6',1,'.jpg'

exec insertDesign 'design name 1','1/5/2014','decription 1 design','quotation 1 design','.jpg',1,1
exec insertDesign 'design name 2','1/5/2014','decription 2 design','quotation 2 design','.jpg',2,2
exec insertDesign 'design name 3','1/5/2014','decription 3 design','quotation 3 design','.jpg',3,3
exec insertDesign 'design name 4','1/5/2014','decription 4 design','quotation 4 design','.jpg',1,2
exec insertDesign 'design name 5','1/5/2014','decription 5 design','quotation 5 design','.jpg',1,4
exec insertDesign 'design name 6','1/5/2014','decription 6 design','quotation 6 design','.jpg',3,5
insert into StaffCompetition values(1,1)
insert into StaffCompetition values(2,1)
insert into StaffCompetition values(3,1)
insert into StaffCompetition values(1,2)
insert into StaffCompetition values(2,2)
insert into Mark values(1,1,4)
insert into Mark values(2,2,4)
insert into Mark values(3,3,4)
insert into Mark values(1,4,4)
insert into Mark values(1,5,4)
insert into Mark values(1,6,4)
insert into Mark values(1,3,4)
insert into Mark values(1,1,3)
insert into Mark values(2,2,5)
insert into Mark values(3,3,6)
insert into Mark values(1,4,3)
insert into Mark values(1,5,4)
insert into Mark values(1,6,6)
insert into Mark values(1,3,2)
--delete from Student
--delete from Competition
--delete from Staff
--delete from StaffCompetition
select top 1 c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage,COUNT(d.comId) as countDesign from Competition c inner join Design d on c.comId=d.comId where DATEDIFF(D,startDate,GETDATE())>0 and DATEDIFF(D,endDate,GETDATE())<0 group by c.comId,c.name,c.startDate,c.endDate,c.description,c.compImage order by countDesign desc
select * from Competition
select * from Design
select c.comId,c.name, COUNT(s.fullName) as NumberOfStaff from Competition c 
	inner join StaffCompetition sc on c.comId=sc.comId 
	inner join Staff s on sc.staffId=s.staffId
	group by c.comId,c.name
select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName,COUNT(s.fullName) as NumberOfStaff from Competition c inner join Manager m on c.managerId= m.managerId inner join StaffCompetition sc on c.comId=sc.comId inner join Staff s on sc.staffId=s.staffId group by c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName order by c.name asc
select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName 
from Competition c inner join Manager m on c.managerId=m.managerId order by c.name asc
select * from Student where fullName like '%st1%' order by userName asc
select top 1 * from Design join Competition on Design.comId = Competition.comId where Competition.name like '%com%' ORDER BY designId DESC
select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName,COUNT(s.fullName) as NumberOfStaff from Competition c inner join Manager m on c.managerId= m.managerId inner join StaffCompetition sc on c.comId=sc.comId inner join Staff s on sc.staffId=s.staffId where c.name like '%co%' group by c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName order by c.name asc
select d.designId,d.name,d.postDate,c.name as comName from Design d inner join Competition c on d.comId=c.comId inner join Student st on d.studentId=st.studentId where st.studentId=1 and c.name like '%c%'
select d.designId,d.name,d.postDate,c.name as comName from Design d inner join Competition c on d.comId=c.comId inner join Student st on d.studentId=st.studentId where st.studentId=1 and d.name like '%1%'
select c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName,COUNT(s.fullName) as NumberOfStaff from Competition c inner join Manager m on c.managerId= m.managerId inner join StaffCompetition sc on c.comId=sc.comId inner join Staff s on sc.staffId=s.staffId group by c.comId,c.name,c.firstPrize,c.secondPrize,c.thirdPrize,c.postDate,c.startDate,c.endDate,c.[description],c.quotation,m.userName order by c.comId asc